const express = require('express');
const router = express.Router();
const connection = require('./db/conn'); // 复用现有的数据库连接
const { v4: uuidv4 } = require('uuid'); // 用于生成UUID

// GET /api/tools?category=text&limit=50&page=1&search=keyword
router.get('/', async (req, res) => {
  try {
    const {
      category,
      limit = 50,
      page = 1,
      search,
      sort = 'date_added',
      order = 'DESC'
    } = req.query;

    let sql = `SELECT id, name, tagline, url, logo_url, category, category_cn, date_added, date_updated 
               FROM ai_tools 
               WHERE is_alive = 1`;
    const params = [];

    // 分类筛选
    if (category && category.trim() !== '') {
      sql += ' AND category = ?';
      params.push(category.trim());
    }

    // 关键词搜索
    if (search && search.trim() !== '') {
      sql += ' AND (name LIKE ? OR tagline LIKE ? OR category LIKE ?)';
      const searchTerm = `%${search.trim()}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }

    // 排序
    const validSortFields = ['date_added', 'date_updated', 'name', 'category'];
    const validOrders = ['ASC', 'DESC'];
    const sortField = validSortFields.includes(sort) ? sort : 'date_added';
    const sortOrder = validOrders.includes(order.toUpperCase()) ? order.toUpperCase() : 'DESC';

    sql += ` ORDER BY ${sortField} ${sortOrder}`;

    // 分页
    const pageNum = Math.max(1, parseInt(page));
    const limitNum = Math.min(100, Math.max(1, parseInt(limit))); // 限制最大100条
    const offset = (pageNum - 1) * limitNum;

    sql += ' LIMIT ? OFFSET ?';
    params.push(limitNum, offset);

    // 执行查询
    connection.query(sql, params, (err, results) => {
      if (err) {
        console.error('查询AI工具列表失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '查询失败',
          data: null,
          error: err.message
        });
      }

      // 获取总数（用于分页信息）
      let countSql = `SELECT COUNT(*) as total FROM ai_tools WHERE is_alive = 1`;
      const countParams = [];

      if (category && category.trim() !== '') {
        countSql += ' AND category = ?';
        countParams.push(category.trim());
      }

      if (search && search.trim() !== '') {
        countSql += ' AND (name LIKE ? OR tagline LIKE ? OR category LIKE ?)';
        const searchTerm = `%${search.trim()}%`;
        countParams.push(searchTerm, searchTerm, searchTerm);
      }

      connection.query(countSql, countParams, (countErr, countResults) => {
        if (countErr) {
          console.error('查询总数失败:', countErr);
          return res.status(500).json({
            code: 500,
            msg: '查询总数失败',
            data: null,
            error: countErr.message
          });
        }

        const total = countResults[0].total;
        const totalPages = Math.ceil(total / limitNum);

        res.json({
          code: 200,
          msg: '查询成功',
          data: {
            list: results,
            pagination: {
              current_page: pageNum,
              per_page: limitNum,
              total: total,
              total_pages: totalPages,
              has_next: pageNum < totalPages,
              has_prev: pageNum > 1
            },
            filters: {
              category: category || null,
              search: search || null,
              sort: sortField,
              order: sortOrder
            }
          }
        });
      });
    });

  } catch (error) {
    console.error('AI工具查询接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

// GET /api/tools/categories - 获取所有分类及其工具数量
router.get('/categories', async (req, res) => {
  try {
    // 定义分类映射
    const categoryMap = [
      {
        title: '文本生成类工具',
        value: 'text'
      },
      {
        title: '图像生成类工具',
        value: 'image'
      },
      {
        title: '代码生成类工具',
        value: 'code'
      },
      {
        title: '视频生成类工具',
        value: 'video'
      },
      {
        title: '音频/语音类工具',
        value: 'audio'
      },
      {
        title: '效率工具类',
        value: 'productivity'
      },
      {
        title: '设计工具类',
        value: 'design'
      }
    ];

    // 查询每个分类的工具数量
    const sql = `
      SELECT category, COUNT(*) as count 
      FROM ai_tools 
      WHERE is_alive = 1 
      GROUP BY category
    `;

    connection.query(sql, (err, countResults) => {
      if (err) {
        console.error('查询分类统计失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '查询分类统计失败',
          data: null,
          error: err.message
        });
      }

      // 创建分类计数映射
      const countMap = {};
      countResults.forEach(row => {
        countMap[row.category] = row.count;
      });

      // 合并分类信息和计数
      const results = categoryMap.map(category => ({
        title: category.title,
        value: category.value,
        count: countMap[category.value] || 0
      }));

      // 计算总数
      const totalCount = Object.values(countMap).reduce((sum, count) => sum + count, 0);

      res.json({
        code: 200,
        msg: '查询成功',
        data: {
          categories: results,
          total: totalCount
        }
      });
    });

  } catch (error) {
    console.error('分类查询接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});



// GET /api/tools/:id - 获取单个工具详情
router.get('/:id', (req, res) => {
  try {
    const { id } = req.params;

    if (!id || typeof id !== 'string' || id.length !== 36) {
      return res.status(400).json({
        code: 400,
        msg: '无效的工具ID格式（需要UUID格式）',
        data: null
      });
    }

    const sql = `SELECT id, name, tagline, url, category, category_cn, date_added, date_updated 
                 FROM ai_tools 
                 WHERE id = ? AND is_alive = 1`;

    connection.query(sql, [id], (err, results) => {
      if (err) {
        console.error('查询工具详情失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '查询失败',
          data: null,
          error: err.message
        });
      }

      if (results.length === 0) {
        return res.status(404).json({
          code: 404,
          msg: '工具不存在',
          data: null
        });
      }

      res.json({
        code: 200,
        msg: '查询成功',
        data: results[0]
      });
    });

  } catch (error) {
    console.error('工具详情查询接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

// POST /api/tools/visit - 记录网站访问量
router.post('/visit', async (req, res) => {
  try {
    const { url, session_id } = req.body;

    // 验证必需参数
    if (!url || typeof url !== 'string') {
      return res.status(400).json({
        code: 400,
        msg: 'URL参数是必需的',
        data: null
      });
    }

    // 获取客户端信息
    const user_ip = req.ip || req.connection.remoteAddress || req.socket.remoteAddress || 
                   (req.connection.socket ? req.connection.socket.remoteAddress : null);
    const user_agent = req.get('User-Agent') || '';
    const referer = req.get('Referer') || '';
    const visit_id = uuidv4();

    // 插入访问记录
    const insertSql = `
      INSERT INTO visit_stats (id, url, user_ip, user_agent, referer, visit_time, session_id)
      VALUES (?, ?, ?, ?, ?, NOW(), ?)
    `;

    connection.query(insertSql, [visit_id, url, user_ip, user_agent, referer, session_id], (err, result) => {
      if (err) {
        console.error('记录访问量失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '记录访问量失败',
          data: null,
          error: err.message
        });
      }

      // 更新汇总统计（按日期和URL）
      const today = new Date().toISOString().split('T')[0]; // 获取今天的日期 YYYY-MM-DD
      const updateSummarySql = `
        INSERT INTO visit_summary (id, url, visit_date, visit_count, unique_visitors)
        VALUES (?, ?, ?, 1, 1)
        ON DUPLICATE KEY UPDATE
        visit_count = visit_count + 1,
        unique_visitors = (
          SELECT COUNT(DISTINCT user_ip) 
          FROM visit_stats 
          WHERE url = VALUES(url) AND DATE(visit_time) = VALUES(visit_date)
        )
      `;

      const summary_id = uuidv4();
      connection.query(updateSummarySql, [summary_id, url, today], (summaryErr, summaryResult) => {
        if (summaryErr) {
          console.error('更新访问汇总失败:', summaryErr);
          // 即使汇总更新失败，也返回成功，因为主要记录已经保存
        }

        res.json({
          code: 200,
          msg: '访问记录成功',
          // data: {
          //   visit_id: visit_id,
          //   url: url,
          //   visit_time: new Date().toISOString()
          // }
        });
      });
    });

  } catch (error) {
    console.error('访问记录接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

// GET /api/tools/visit/stats - 获取访问总数统计
router.get('/visit/stats', async (req, res) => {
  try {
    // 基于visit_stats表的原始访问记录进行统计
    const sql = `
      SELECT 
        COUNT(*) as total_visits,
        COUNT(DISTINCT user_ip) as total_unique_visitors,
        COUNT(DISTINCT url) as unique_urls,
        COUNT(DISTINCT DATE(visit_time)) as active_days,
        MIN(DATE(visit_time)) as first_visit_date,
        MAX(DATE(visit_time)) as last_visit_date
      FROM visit_stats
    `;

    connection.query(sql, (err, results) => {
      if (err) {
        console.error('查询访问统计失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '查询访问统计失败',
          data: null,
          error: err.message
        });
      }

      const stats = results[0] || {
        total_visits: 0,
        total_unique_visitors: 0,
        unique_urls: 0,
        active_days: 0,
        first_visit_date: null,
        last_visit_date: null
      };

      res.json({
        code: 200,
        msg: '查询成功',
        data: {
          total_visits: stats.total_visits || 0,
          total_unique_visitors: stats.total_unique_visitors || 0,
          unique_urls: stats.unique_urls || 0,
          active_days: stats.active_days || 0,
          first_visit_date: stats.first_visit_date,
          last_visit_date: stats.last_visit_date
        }
      });
    });

  } catch (error) {
    console.error('访问统计查询接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

// POST /api/tools - 新增AI工具
router.post('/', async (req, res) => {
  try {
    const { name, tagline, url, logo_url, category, category_cn } = req.body;

    // 验证必需参数
    if (!name || !tagline || !url || !category) {
      return res.status(400).json({
        code: 400,
        msg: '缺少必需参数：name, tagline, url, category',
        data: null
      });
    }

    // 验证分类是否有效
    const validCategories = ['text', 'image', 'code', 'video', 'audio', 'productivity', 'design'];
    if (!validCategories.includes(category)) {
      return res.status(400).json({
        code: 400,
        msg: '无效的分类，支持的分类：' + validCategories.join(', '),
        data: null
      });
    }

    // 验证URL格式
    try {
      new URL(url);
    } catch (urlError) {
      return res.status(400).json({
        code: 400,
        msg: '无效的URL格式',
        data: null
      });
    }

    const id = uuidv4();
    const now = new Date();

    const sql = `
      INSERT INTO ai_tools (id, name, tagline, url, logo_url, category, category_cn, date_added, date_updated, is_alive)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
    `;

    const params = [
      id,
      name.trim(),
      tagline.trim(),
      url.trim(),
      logo_url ? logo_url.trim() : null,
      category,
      category_cn || null,
      now,
      now
    ];

    connection.query(sql, params, (err, result) => {
      if (err) {
        console.error('新增AI工具失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '新增失败',
          data: null,
          error: err.message
        });
      }

      res.json({
        code: 200,
        msg: '新增成功',
        data: {
          id: id,
          name: name.trim(),
          tagline: tagline.trim(),
          url: url.trim(),
          logo_url: logo_url ? logo_url.trim() : null,
          category: category,
          category_cn: category_cn || null,
          date_added: now,
          date_updated: now,
          is_alive: 1
        }
      });
    });

  } catch (error) {
    console.error('新增AI工具接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

// PUT /api/tools/:id - 修改AI工具
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, tagline, url, logo_url, category, category_cn } = req.body;

    // 验证ID格式
    if (!id || typeof id !== 'string' || id.length !== 36) {
      return res.status(400).json({
        code: 400,
        msg: '无效的工具ID格式（需要UUID格式）',
        data: null
      });
    }

    // 验证必需参数
    if (!name || !tagline || !url || !category) {
      return res.status(400).json({
        code: 400,
        msg: '缺少必需参数：name, tagline, url, category',
        data: null
      });
    }

    // 验证分类是否有效
    const validCategories = ['text', 'image', 'code', 'video', 'audio', 'productivity', 'design'];
    if (!validCategories.includes(category)) {
      return res.status(400).json({
        code: 400,
        msg: '无效的分类，支持的分类：' + validCategories.join(', '),
        data: null
      });
    }

    // 验证URL格式
    try {
      new URL(url);
    } catch (urlError) {
      return res.status(400).json({
        code: 400,
        msg: '无效的URL格式',
        data: null
      });
    }

    // 先检查记录是否存在
    const checkSql = 'SELECT id FROM ai_tools WHERE id = ? AND is_alive = 1';
    connection.query(checkSql, [id], (checkErr, checkResults) => {
      if (checkErr) {
        console.error('检查工具是否存在失败:', checkErr);
        return res.status(500).json({
          code: 500,
          msg: '检查工具是否存在失败',
          data: null,
          error: checkErr.message
        });
      }

      if (checkResults.length === 0) {
        return res.status(404).json({
          code: 404,
          msg: '工具不存在',
          data: null
        });
      }

      const now = new Date();
      const updateSql = `
        UPDATE ai_tools 
        SET name = ?, tagline = ?, url = ?, logo_url = ?, category = ?, category_cn = ?, date_updated = ?
        WHERE id = ? AND is_alive = 1
      `;

      const params = [
        name.trim(),
        tagline.trim(),
        url.trim(),
        logo_url ? logo_url.trim() : null,
        category,
        category_cn || null,
        now,
        id
      ];

      connection.query(updateSql, params, (err, result) => {
        if (err) {
          console.error('修改AI工具失败:', err);
          return res.status(500).json({
            code: 500,
            msg: '修改失败',
            data: null,
            error: err.message
          });
        }

        if (result.affectedRows === 0) {
          return res.status(404).json({
            code: 404,
            msg: '工具不存在或已被删除',
            data: null
          });
        }

        res.json({
          code: 200,
          msg: '修改成功',
          data: {
            id: id,
            name: name.trim(),
            tagline: tagline.trim(),
            url: url.trim(),
            logo_url: logo_url ? logo_url.trim() : null,
            category: category,
            category_cn: category_cn || null,
            date_updated: now
          }
        });
      });
    });

  } catch (error) {
    console.error('修改AI工具接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

// DELETE /api/tools/:id - 删除单个AI工具
// DELETE /api/tools - 批量删除AI工具（通过请求体传递ID数组）
router.delete('/:id?', async (req, res) => {
  try {
    const { id } = req.params;
    const { ids } = req.body;

    let targetIds = [];

    // 单个删除
    if (id) {
      // 验证ID格式
      if (typeof id !== 'string' || id.length !== 36) {
        return res.status(400).json({
          code: 400,
          msg: '无效的工具ID格式（需要UUID格式）',
          data: null
        });
      }
      targetIds = [id];
    }
    // 批量删除
    else if (ids && Array.isArray(ids)) {
      if (ids.length === 0) {
        return res.status(400).json({
          code: 400,
          msg: 'ID数组不能为空',
          data: null
        });
      }

      if (ids.length > 100) {
        return res.status(400).json({
          code: 400,
          msg: '一次最多删除100条记录',
          data: null
        });
      }

      // 验证所有ID格式
      for (const itemId of ids) {
        if (typeof itemId !== 'string' || itemId.length !== 36) {
          return res.status(400).json({
            code: 400,
            msg: `无效的工具ID格式：${itemId}（需要UUID格式）`,
            data: null
          });
        }
      }
      targetIds = ids;
    }
    else {
      return res.status(400).json({
        code: 400,
        msg: '请提供要删除的工具ID或ID数组',
        data: null
      });
    }

    // 构建删除SQL（软删除，设置is_alive=0）
    const placeholders = targetIds.map(() => '?').join(',');
    const deleteSql = `UPDATE ai_tools SET is_alive = 0, date_updated = ? WHERE id IN (${placeholders}) AND is_alive = 1`;
    const params = [new Date(), ...targetIds];

    connection.query(deleteSql, params, (err, result) => {
      if (err) {
        console.error('删除AI工具失败:', err);
        return res.status(500).json({
          code: 500,
          msg: '删除失败',
          data: null,
          error: err.message
        });
      }

      const deletedCount = result.affectedRows;

      if (deletedCount === 0) {
        return res.status(404).json({
          code: 404,
          msg: '没有找到要删除的工具或工具已被删除',
          data: null
        });
      }

      res.json({
        code: 200,
        msg: `成功删除 ${deletedCount} 个工具`,
        data: {
          deleted_count: deletedCount,
          deleted_ids: targetIds.slice(0, deletedCount)
        }
      });
    });

  } catch (error) {
    console.error('删除AI工具接口异常:', error);
    res.status(500).json({
      code: 500,
      msg: '服务器内部错误',
      data: null,
      error: error.message
    });
  }
});

module.exports = router;